﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;
using DBUtil.MetaData;
using System.Linq;
using System.Collections.Concurrent;
using System.Threading;
using MySqlConnector;
using DBUtil.Provider.MySql.MetaData;
using DotNetCommon.Data;
using Index = DBUtil.MetaData.Index;
using DotNetCommon.Extensions;
using System.IO;

namespace DBUtil.Provider.MySql
{
    /// <summary>
    /// MySql操作对象
    /// </summary>
    public partial class MySqlManage : DBManage
    {
        /// <summary>
        /// 根据DBAccess创建DBManage
        /// </summary>
        /// <param name="db"></param>
        public MySqlManage(DBAccess db) : base(db) { }        

        #region 修改元数据
        /// <summary>
        /// 重命名表名
        /// </summary>
        /// <param name="oldName">旧表名</param>
        /// <param name="newName">新表名</param>
        /// <param name="schemaName">指定模式名</param>
        /// <returns></returns>
        public override Result RenameTable(string oldName, string newName, string schemaName = null)
        {
            oldName = db.ParseObjectName(oldName).NameQuoted;
            newName = db.ParseObjectName(newName).NameQuoted;
            return Result.Wrap(() =>
            {
                string sql = $"RENAME table {oldName} to {newName}";
                db.ExecuteSql(sql);
            });
        }

        /// <summary>
        /// 重命名列名
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="oldName">旧列名</param>
        /// <param name="newName">新列名</param>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public override Result RenameColumn(string tableName, string oldName, string newName, string schemaName = null)
        {
            return Result.Wrap(() =>
            {
                var table = new MySqlTable() { Name = tableName };
                FillColumns(table);
                var column = table.Columns.Where(col => col.Name.ToUpper() == oldName.ToUpper()).FirstOrDefault();
                var sql = GetModifyColumnSql(column, false, newName);
                db.ExecuteSql(sql);
            });
        }

        /// <summary>
        /// 修改表说明
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="desc">说明信息</param>
        /// <param name="schemaName">指定schema名称</param>
        public override Result UpdateTableDescription(string tableName, string desc, string schemaName = null)
        {
            return Result.Wrap(() =>
            {
                string sql = $"alter table {tableName} comment '{desc}';";
                db.ExecuteSql(sql);
            });
        }

        /// <summary>
        /// 删除指定表
        /// </summary>
        /// <param name="tableName">要删除的表</param>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public override Result DropTable(string tableName, string schemaName = null)
        {
            return Result.Wrap(() =>
            {
                string sql = $"DROP TABLE {tableName}";
                db.ExecuteSql(sql);
            });
        }

        /// <summary>
        /// 删除指定表,如果存在
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public override Result DropTableIfExist(string tableName, string schemaName = null)
        {
            return Result.Wrap(() =>
            {
                string sql = $"DROP TABLE IF EXISTS {tableName}";
                db.ExecuteSql(sql);
            });
        }

        /// <summary>
        /// 修改列说明
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="desc">说明信息</param>
        /// <param name="schemaName">指定schema名称</param>
        public override Result UpdateColumnDescription(string tableName, string colName, string desc, string schemaName = null)
        {
            return Result.Wrap(() =>
            {
                var table = new MySqlTable() { Name = tableName };
                FillColumns(table);
                var column = table.Columns.Where(col => col.Name.ToUpper() == colName?.ToUpper()).FirstOrDefault();
                if (column == null) throw new Exception($"没有找到列{colName}");
                column.Desc = desc;
                var sql = GetModifyColumnSql(column);
                db.ExecuteSql(sql);
            });
        }

        /// <summary>
        /// 修改列是否可为空
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="isNullAble">是否可为空</param>
        /// <param name="schemaName">指定schema名称</param>
        public override Result UpdateColumnNullAble(string tableName, string colName, bool isNullAble, string schemaName = null)
        {
            return Result.Wrap(() =>
            {
                var table = new MySqlTable() { Name = tableName };
                FillColumns(table);
                var column = table.Columns.Where(col => col.Name.ToUpper() == colName?.ToUpper()).FirstOrDefault();
                if (column == null) throw new Exception($"没有找到列{colName}");
                if (column.IsPrimaryKey && isNullAble == true) throw new Exception("主键不允许设置为空!");
                if (column.IsIdentity && isNullAble == true) throw new Exception("自增不允许设置为空!");
                column.IsNullAble = isNullAble;
                var sql = GetModifyColumnSql(column);
                db.ExecuteSql(sql);
            });
        }

        /// <summary>
        /// 修改列是否唯一
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="isUnique">是否唯一</param>
        /// <param name="schemaName">指定schema名称</param>
        public override Result UpdateColumnUnique(string tableName, string colName, bool isUnique, string schemaName = null)
        {
            return Result.Wrap(() =>
            {
                var table = new MySqlTable() { Name = tableName };
                FillColumns(table);
                var column = table.Columns.Where(col => col.Name.ToUpper() == colName?.ToUpper()).FirstOrDefault();
                if (column == null) throw new Exception($"没有找到列{colName}");
                if (column.IsPrimaryKey && isUnique == false) throw new Exception("主键必须唯一!");
                if (column.IsIdentity && isUnique == false) throw new Exception("自增必须唯一!");
                column.IsUnique = isUnique;
                var sql = GetModifyColumnSql(column);
                db.ExecuteSql(sql);
            });
        }

        /// <summary>
        /// 修改列是否自增
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="identity">自增信息,为空,则表示不自增</param>
        /// <param name="schemaName">指定schema名称</param>
        public override Result UpdateColumnIdentity(string tableName, string colName, string identity, string schemaName = null)
        {
            return Result.Wrap(() =>
            {
                var table = new MySqlTable() { Name = tableName };
                FillColumns(table);
                var column = table.Columns.Where(col => col.Name.ToUpper() == colName?.ToUpper()).FirstOrDefault();
                if (column == null) throw new Exception($"没有找到列{colName}");
                if (identity.IsNullOrEmptyOrWhiteSpace()) column.IsIdentity = false;
                else column.IsIdentity = true;
                var sql = GetModifyColumnSql(column);
                db.ExecuteSql(sql);
            });
        }

        /// <summary>
        /// 修改列类型
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="type">列类型</param>
        /// <param name="schemaName">指定schema名称</param>
        public override Result UpdateColumnType(string tableName, string colName, string type, string schemaName = null)
        {
            return Result.Wrap(() =>
            {
                var table = new MySqlTable() { Name = tableName };
                FillColumns(table);
                var column = table.Columns.Where(col => col.Name.ToUpper() == colName?.ToUpper()).FirstOrDefault();
                if (column == null) throw new Exception($"没有找到列{colName}");
                column.TypeString = type;
                var sql = GetModifyColumnSql(column, false);
                db.ExecuteSql(sql);
            });
        }

        /// <summary>
        /// 获取列修改语句
        /// </summary>
        /// <param name="column">列模型</param>
        /// <param name="isAdd">是否新增</param>
        /// <param name="newName">重命名时的新名称</param>
        /// <returns></returns>
        public string GetModifyColumnSql(Column column, bool isAdd = false, string newName = "")
        {
            if (column == null) return string.Empty;
            string sql = $"alter table {column.TableName} {(isAdd ? "add" : "modify")} column {column.Name} {column.TypeString}";
            if (newName.IsNotNullOrEmptyOrWhiteSpace())
            {
                sql = $"alter table {column.TableName} change {column.Name} {newName} {column.TypeString}"; ;
            }
            if (column.IsIdentity)
            {
                sql += " auto_increment";
            }
            if (column.IsPrimaryKey && isAdd)
            {
                //只有新增的时候需要
                sql += " primary key";
            }
            if (!column.IsNullAble)
            {
                sql += " not null";
            }
            if (column.HasDefault)
            {
                sql += $" default {column.Default}";
            }
            if (column.IsUnique)
            {
                sql += " unique";
            }
            var mysqlColumn = column as MySqlColumn;
            if (mysqlColumn.IsComputed)
            {
                sql += $" generated always as {mysqlColumn.ComputedDefinition}";
                if (mysqlColumn.IsPersisted == true)
                {
                    sql += " STORED";
                }
            }
            if (column.Desc.IsNotNullOrEmptyOrWhiteSpace())
            {
                sql += $" comment '{db.DealSqlInject(column.Desc)}'";
            }
            return sql;
        }

        /// <summary>
        /// 删除指定表的指定列
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">要删除的列名</param>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public override Result DropColumn(string tableName, string columnName, string schemaName = null)
        {
            return Result.Wrap(() =>
            {
                string sql = $"ALTER TABLE {tableName} DROP {columnName}";
                db.ExecuteSql(sql);
            });
        }

        /// <summary>
        /// 获取列定义数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <returns></returns>
        public Column GetColumn(string tableName, string colName)
        {
            if (string.IsNullOrWhiteSpace(tableName)) return null;
            var table = new MySqlTable() { Name = tableName };
            FillColumns(table);
            return table.Columns.Where(col => col.Name.ToUpper() == colName?.ToUpper()).FirstOrDefault();
        }

        /// <summary>
        /// 修改指定表的指定列是否可以为空
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <param name="canNull">是否可空</param>
        /// <param name="columnType">列类型,如果columnType为空,程序会自动探索列类型</param>
        /// <returns></returns>
        public Result AlterColumnNullAble(string tableName, string columnName, bool canNull, string columnType)
        {
            return ModifyColumn(tableName, columnName, col => { col.TypeString = string.IsNullOrWhiteSpace(columnType) ? col.TypeString : columnType; col.IsNullAble = canNull; });
        }

        /// <summary>
        /// 给指定列修改默认值
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <param name="def">默认值</param>
        /// <returns></returns>
        public Result AlterColumnDefault(string tableName, string columnName, string def)
        {
            return ModifyColumn(tableName, columnName, col => { col.HasDefault = true; col.Default = def; });
        }

        /// <summary>
        /// 修改列属性
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <param name="action">修改逻辑</param>
        /// <returns></returns>
        public Result ModifyColumn(string tableName, string columnName, Action<Column> action)
        {
            if (action == null) return Result.Ok();
            return Result.Wrap(() =>
            {
                Column column = GetColumn(tableName, columnName);
                if (column == null) throw new Exception($"未找到列:{columnName}");
                action(column);
                var sql = GetModifyColumnSql(column);
                db.ExecuteSql(sql);
            });
        }

        /// <summary>
        /// 删除指定表指定列的默认值
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <returns></returns>
        public Result DropColumnDefault(string tableName, string columnName)
        {
            return ModifyColumn(tableName, columnName, col => col.HasDefault = false);
        }

        /// <summary>
        /// 给指定表添加一列
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="column">列</param>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public override Result AddColumn(string tableName, Column column, string schemaName = null)
        {
            if (column == null) return Result.NotOk("必须指定列属性!");
            if (string.IsNullOrWhiteSpace(column.TableName))
            {
                column.TableName = tableName;
            }
            if (string.IsNullOrWhiteSpace(column.TableName)) return Result.NotOk("必须指定表名称");
            string sql = GetModifyColumnSql(column, true);
            sql = sql.Replace(" modify ", " add ");
            return Result.Wrap(() =>
            {
                db.ExecuteSql(sql);
            });

        }

        /// <summary>
        /// 设置指定列是否是唯一的
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名,可以是多个列,如:"Name,Age"</param>
        /// <param name="canUnique">是否是唯一的</param>
        public Result AlterColumnUnique(string tableName, string columnName, bool canUnique)
        {
            if (string.IsNullOrWhiteSpace(tableName) || string.IsNullOrWhiteSpace(columnName))
            {
                return Result.NotOk("表名或列名不能为空!");
            }
            var arr = columnName.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
            var sql = $"alter table {tableName} add constraint unique_{tableName}_{string.Join("_", arr)} unique({string.Join(",", arr)});";
            return Result.Wrap(() =>
            {
                db.ExecuteSql(sql);
            });
        }

        /// <summary>
        /// 创建表,注意创建的内容有:表和列说明/列类型/列自增/列的非空/列的唯一/列的默认值/主键
        /// </summary>
        /// <param name="table">表结构</param>
        public override Result CreateTable(Table table)
        {
            var _table = table as MySqlTable;
            if (_table == null) return Result.NotOk("必须指定表属性!");
            //最终create语句
            string sql = $@"create table `{(table.SchemaName.IsNullOrEmptyOrWhiteSpace() ? db.DataBase : _table.SchemaName)}`.`{_table.Name}` (
";
            var columns = new List<string>();
            //主键列名称,如:name,age
            var prinames = new List<string>();
            //表说明以及列说明语句
            string sqltabledesc = "";
            if (!string.IsNullOrWhiteSpace(_table.Desc))
            {
                //给表加说明
                sqltabledesc += $" comment '{db.DealSqlInject(_table.Desc)}'";
            }
            _table.Columns.ForEach(i =>
            {
                var arr = new List<string>();
                arr.Add($"`{i.Name}`");
                arr.Add($"{i.TypeString}");
                if (!i.IsNullAble)
                {
                    arr.Add($"not null");
                }
                if (i.IsIdentity)
                {
                    arr.Add($"auto_increment");
                }
                if (i.IsUnique)
                {
                    arr.Add($"unique");
                }
                if (!string.IsNullOrWhiteSpace(i.Default))
                {
                    arr.Add($"default ('{i.Default}')");
                }
                if (i.IsComputed)
                {
                    if (i.IsPersisted == true)
                    {
                        var virtualSql = $" generated always as {i.ComputedDefinition}";
                        if (i.IsPersisted == true)
                        {
                            virtualSql += " STORED";
                        }
                        arr.Add(virtualSql);
                    }
                }
                if (!string.IsNullOrWhiteSpace(i.Desc))
                {
                    arr.Add($"comment '{i.Desc}'");
                }
                if (i.IsPrimaryKey)
                {
                    prinames.Add(i.Name);
                }
                columns.Add(arr.ToStringSeparated(" "));
            });
            sql += "\t" + columns.ToStringSeparated(",\r\n  ");
            var prikeys = prinames.Select(i => $"`{i}`").ToStringSeparated(",");
            if (prikeys.Length > 0)
            {
                sql += $"\r\n  PRIMARY KEY ({prikeys})";
            }
            sql += "\r\n)";
            //表引擎
            if (_table.Engine.IsNotNullOrEmptyOrWhiteSpace())
            {
                sql += $" ENGINE={_table.Engine}";
            }
            //字符集
            if (_table.Collection.IsNotNullOrEmptyOrWhiteSpace())
            {
                var cs = "";
                if (_table.Collection == "utf8_general_ci")
                {
                    cs = "utf8";
                }
                else if (_table.Collection == "utf8mb4_general_ci")
                {
                    cs = "utf8mb4";
                }
                if (cs == "")
                {
                    cs = db.SelectScalar<string>($"select CHARACTER_SET_NAME from information_schema.COLLATIONS c where c.COLLATION_NAME ='{_table.Collection}'");
                }
                if (cs.Length > 0)
                {
                    sql += $" DEFAULT CHARSET={cs}";
                }
            }
            //表注释
            if (_table.Desc.IsNotNullOrEmptyOrWhiteSpace())
            {
                sql += $" COMMENT='{db.DealSqlInject(_table.Desc)}'";
            }

            return Result.Wrap(() =>
            {
                db.ExecuteSql(sql);
            });
        }

        /// <summary>
        /// 返回触发器集合
        /// </summary>
        /// <param name="tableName">所属的表名称</param>
        /// <param name="schemaName"></param>
        /// <returns></returns>
        public override List<Trigger> ShowTableTriggers(string tableName, string schemaName)
        {
            if (string.IsNullOrWhiteSpace(tableName)) return new List<Trigger>();
            List<Trigger> triggers = new List<Trigger>();
            string sql = $"select * from information_schema.`TRIGGERS` where EVENT_OBJECT_SCHEMA='{db.DataBase}' and EVENT_OBJECT_TABLE='{tableName}'";
            DataTable dt = db.SelectDataTable(sql);
            for (int i = 0, len = dt.Rows.Count; i < len; i++)
            {
                string name = dt.Rows[i]["TRIGGER_NAME"].ToString();
                var condition = dt.Rows[i]["EVENT_MANIPULATION"].ToString().ToUpper();
                bool isUpdate = condition == "UPDATE";
                bool isDelete = condition == "DELETE";
                bool isInsert = condition == "INSERT";
                bool isAfter = dt.Rows[i]["ACTION_TIMING"].ToString() == "AFTER" ? false : true;
                var createtime = DateTime.Parse(dt.Rows[i]["CREATED"].ToString());
                bool isinsteadof = false;
                sql = $"show create trigger {name}";
                DataTable dt2 = db.SelectDataTable(sql);
                string createSql = dt2.Rows[0]["SQL Original Statement"].ToString();
                Trigger tri = new MySqlTrigger()
                {
                    Name = name,
                    CreateSql = createSql,
                    TableName = tableName,
                    IsInsert = isInsert,
                    IsUpdate = isUpdate,
                    IsDelete = isDelete,
                    IsAfter = isAfter,
                    IsInsteadof = isinsteadof,
                    CreateTime = createtime
                };
                triggers.Add(tri);
            }
            return triggers;
        }
        #endregion        
    }    
}
